/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.VePOJO;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import Utils.MySqlDataAccessHelper;

/**
 *
 * @author KimLoan
 */
public class VeDAO {
     public boolean themVe(VePOJO ve) {
        boolean kq = false;
        String sql = String.format("INSERT INTO ve(idGhe,GiaVe,SoLuongVe,SoLuongBan,TinhTrangVe)"
                + " VALUES(%d,%f,%d,%d,%d)",ve.getMaGhe(),ve.getGiaVe(),ve.getSoLuongVe(),ve.getSoLuongVeBan(),
                ve.getTrangThai());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Them Ve thanh cong
        }

        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xoa Ve
     * @return
     */
    public boolean xoaVe(String maVe) {

        boolean kq = false;
        try {
            String sql = "delete from Ve where idVe=" + maVe;
            // String sql = "DELETE FROM Ve WHERE maVe ='"+  maVe+"'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            int n = helper.executeUpdate(sql);
            if (n >= 1) {
                kq = true;//xoa thanh cong
            }
            helper.close();
            return kq;
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            return kq;
        }
    }

    /**
     * Phuong Thuc Cap Nhat Ve
     */
    public boolean capNhatVe(VePOJO ve) {
        boolean kq = false;

        String sql = String.format(
                "UPDATE ve SET idGhe=%d,GiaVe=%f,SoLuongVe=%d,SoLuongBan=%d,TinhTrangVe=%d"
                + " WHERE idVe=%d ",ve.getMaGhe(),ve.getGiaVe(),ve.getSoLuongVe(),ve.getSoLuongVeBan(),
                ve.getTrangThai(),ve.getMaVe());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;//Cap Nhat Ve thanh cong
        }
        helper.close();
        return kq;
    }

    /**
     * Phuong Thuc Xem Danh Ve
     * @return
     */
    public ArrayList<VePOJO> layDanhVeCacMaVe() {
        ArrayList<VePOJO> ds = new ArrayList<VePOJO>();
        try {
            String sql = "SELECT * FROM ve";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                VePOJO ve = new VePOJO();
                ve.setMaVe(rs.getInt("idVe"));
               // ve.setMaLichChieu(rs.getInt("idLichChieu"));
                ve.setMaGhe(rs.getInt("idGhe"));
                ve.setGiaVe(rs.getDouble("GiaVe"));
                ve.setSoLuongVe(rs.getInt("SoLuongVe"));
                ve.setSoLuongVeBan(rs.getInt("SoLuongVeBan"));
                ve.setTrangThai(rs.getInt("TrangThaiVe"));
                ds.add(ve);
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    /**
     * Phuong Thuc Lay Thong Tin Ve
     * @param maVe
     * @return
     */
    public VePOJO layThongTinVe(String maVe) {
        VePOJO ve = null;
        String sql = String.format("SELECT * FROM ve WHERE idVe=%d", maVe);
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                ve = new VePOJO();
                ve.setMaVe(rs.getInt("idVe"));
               //ve.setMaLichChieu(rs.getInt("idLichChieu"));
                ve.setMaGhe(rs.getInt("idGhe"));
                ve.setGiaVe(rs.getDouble("GiaVe"));
                ve.setSoLuongVe(rs.getInt("SoLuongVe"));
                ve.setSoLuongVeBan(rs.getInt("SoLuongVeBan"));
                ve.setTrangThai(rs.getInt("TrangThaiVe"));
            }
            helper.close();
        } catch (SQLException ex) {

            System.out.println(ex.getMessage());
        }
        return ve;
    }
    //Ham Lay danh Ve theo the loai

    public ArrayList<VePOJO> layDSVe_TheoTheLoai(int maTheLoai) {

        ArrayList<VePOJO> dsVe = new ArrayList<VePOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayDSVe_TheoTheLoai(?)}");
            statement.setInt(1, maTheLoai);

            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                VePOJO ve = new VePOJO();
                ve.setMaVe(rs.getInt("idVe"));
                //ve.setMaLichChieu(rs.getInt("idLichChieu"));
                ve.setMaGhe(rs.getInt("idGhe"));
                ve.setGiaVe(rs.getDouble("GiaVe"));
                ve.setSoLuongVe(rs.getInt("SoLuongVe"));
                ve.setSoLuongVeBan(rs.getInt("SoLuongVeBan"));
                ve.setTrangThai(rs.getInt("TrangThaiVe"));
                dsVe.add(ve);

            }
            helper.close();
            return dsVe;
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
            return dsVe;
        }
    }

    public ArrayList<VePOJO> timKiemVe_TheoTenVe(String _tenVe) {

        ArrayList<VePOJO> ds = new ArrayList<VePOJO>();
        try {
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            Connection cn = (Connection) helper.getConnection();
            CallableStatement statement = (CallableStatement) cn.prepareCall("{call sp_LayVeTheoTenVe(?)}");
            statement.setString(1, _tenVe);
            statement.execute();
            ResultSet rs = statement.getResultSet();
            while (rs.next()) {
                VePOJO ve = new VePOJO();
                 ve.setMaVe(rs.getInt("idVe"));
                //ve.setMaLichChieu(rs.getInt("idLichChieu"));
                ve.setMaGhe(rs.getInt("idGhe"));
                ve.setGiaVe(rs.getDouble("GiaVe"));
                ve.setSoLuongVe(rs.getInt("SoLuongVe"));
                ve.setSoLuongVeBan(rs.getInt("SoLuongVeBan"));
                ve.setTrangThai(rs.getInt("TrangThaiVe"));
                ds.add(ve);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return ds;
    }
}
